import datetime
import random
import sqlite3
import os

# --- 配置 ---
MODULE_NAMES = ["高锰酸盐", "氨氮", "总磷", "总氮", "水温", "PH", "溶解氧", "浊度", "电导率", "生物毒性"] # 模块名称列表
NUM_DAYS_TO_GENERATE = 300  # 需要生成数据的天数
_today_midnight = datetime.datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)
START_DATE = _today_midnight - datetime.timedelta(days=NUM_DAYS_TO_GENERATE) # N天前的0点
END_DATE = _today_midnight # 当天的0点 (不包含当天数据，即生成截止到昨天23点的数据)
OUTPUT_FORMAT = "sqlite" # 输出格式: "print", "csv", "sqlite"
CSV_FILENAME = "test_data.csv" # 如果输出格式为 "csv"，指定文件名
DB_FILENAME = "database.db" # 如果输出格式为 "sqlite"，指定数据库文件名
TABLE_NAME = "device_readings" # 数据库表名
MODULE_INACTIVE_PROBABILITY = 0.1 # 模块未启用的概率 (例如 0.1 表示 10%)

# --- 数据生成 ---
data = []
current_time = START_DATE

while current_time < END_DATE:
    timestamp = current_time.strftime('%Y-%m-%d %H:%M:%S')
    
    for module_name in MODULE_NAMES:
        if random.random() < MODULE_INACTIVE_PROBABILITY:
            # 模拟模块未启用，跳过该模块数据生成
            continue
        else:
            # 模块启用，生成数据
            record = {}
            record['timestamp'] = timestamp
            record['device_name'] = module_name
            
            # 特殊处理 PH 值范围 (0-14)
            if module_name == "PH":
                raw_value = random.uniform(0, 14)
            else:
                raw_value = random.uniform(0, 100)
                
            record['value'] = round(raw_value, 3)
            data.append(record)
    
    # 增加一个小时
    current_time += datetime.timedelta(hours=1)

# --- 数据输出 ---
if OUTPUT_FORMAT == "print":
    # 打印表头
    print("timestamp,device_name,value")
    # 打印数据
    for record in data:
        print(f"{record['timestamp']},{record['device_name']},{record['value']}")
        
elif OUTPUT_FORMAT == "csv":
    import csv
    header = ["timestamp", "device_name", "value"]
    try:
        with open(CSV_FILENAME, 'w', newline='', encoding='utf-8') as csvfile:
            writer = csv.DictWriter(csvfile, fieldnames=header)
            writer.writeheader()
            writer.writerows(data)
        print(f"数据已成功写入到 {CSV_FILENAME}")
    except IOError as e:
        print(f"写入 CSV 文件时出错: {e}")
        
elif OUTPUT_FORMAT == "sqlite":
    conn = None
    try:
        # 连接数据库，如果文件不存在则创建
        conn = sqlite3.connect(DB_FILENAME)
        cursor = conn.cursor()

        # 创建表 (如果不存在)
        create_table_sql = '''
        CREATE TABLE IF NOT EXISTS "{}" (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            timestamp TEXT NOT NULL,
            device_name TEXT NOT NULL,
            value REAL,
            UNIQUE(timestamp, device_name)
        )'''.format(TABLE_NAME)
        cursor.execute(create_table_sql)
        
        # 创建索引
        create_index_sql = '''
        CREATE INDEX IF NOT EXISTS idx_readings_timestamp 
        ON "{}" (timestamp)
        '''.format(TABLE_NAME)
        cursor.execute(create_index_sql)

        # 准备插入语句
        insert_sql = '''
        INSERT OR REPLACE INTO "{}" (timestamp, device_name, value)
        VALUES (?, ?, ?)
        '''.format(TABLE_NAME)

        # 准备要插入的数据
        rows_to_insert = [(r['timestamp'], r['device_name'], r['value']) for r in data]

        # 批量插入数据
        cursor.executemany(insert_sql, rows_to_insert)

        # 提交事务
        conn.commit()
        print(f"数据已成功写入到数据库 {DB_FILENAME} 的表 {TABLE_NAME} 中。")
        print(f"共生成 {len(data)} 条记录，涵盖 {len(set(r['timestamp'] for r in data))} 个时间点。")

    except sqlite3.Error as e:
        print(f"写入 SQLite 数据库时出错: {e}")
        if conn:
            conn.rollback() # 如果出错则回滚
    finally:
        if conn:
            conn.close() # 确保关闭连接
else:
    print("无效的输出格式。请选择 'print', 'csv' 或 'sqlite'")
